﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Configuration;
using MySql.Data.MySqlClient;

public class QuestionBLL
{
    string connection = WebConfigurationManager.ConnectionStrings["UCNLinkDB"].ConnectionString;

    public List<QuestionProperty> GetAllQuestionsByDivision(int divId, bool forStudent)
    {
        
        MySqlConnection conn = new MySqlConnection(connection);
        MySqlCommand cmd = new MySqlCommand("SELECT ID, queQuestion, DivisionID, quePlaceholder FROM tblQuestion WHERE DivisionID = @DivisionID  AND queForStudent = @ForStudent", conn);

        cmd.Parameters.Add("@DivisionID", MySqlDbType.Int32);
        cmd.Parameters["@DivisionID"].Value = divId;

        cmd.Parameters.Add("@ForStudent", MySqlDbType.Bit);
        cmd.Parameters["@ForStudent"].Value = forStudent;

        List<QuestionProperty> allQP = new List<QuestionProperty>();

        conn.Open();
        cmd.Connection = conn;

        MySqlDataReader reader = cmd.ExecuteReader();


        if (reader.HasRows)
        {
            foreach (Object read in reader)
            {
                QuestionProperty qp = new QuestionProperty();
                qp.Id = Convert.ToInt32(reader["ID"]);
                qp.QueQuestion = reader["queQuestion"].ToString();
                qp.DivisionId = Convert.ToInt32(reader["DivisionID"]);
                qp.QuePlaceholder = reader["quePlaceholder"].ToString();
                allQP.Add(qp);
            }
        }
        reader.Close();
        cmd.Dispose();
        conn.Close();

        return allQP;
    }

    public int MaxQuestionsPrDivision(int divId)
    {
        int result = 0;
        MySqlConnection conn = new MySqlConnection(connection);
        MySqlCommand cmd = new MySqlCommand("SELECT MAX(ID) AS maxAmount FROM tblQuestion WHERE DivisionID = @DivisionID", conn);

        cmd.Parameters.Add("@DivisionID", MySqlDbType.Int32);
        cmd.Parameters["@DivisionID"].Value = divId;

        conn.Open();
        cmd.Connection = conn;

        MySqlDataReader reader = cmd.ExecuteReader();


        if (reader.HasRows)
        {
            result = Convert.ToInt32(reader["maxAmount"]);
        }
        reader.Close();
        cmd.Dispose();
        conn.Close();

        return result;
    }

    public int InsertQuestion(QuestionProperty qp)
    {
        int result = 0;
        MySqlConnection conn = new MySqlConnection(connection);
        MySqlCommand cmd = new MySqlCommand("INSERT INTO tblQuestion (queQuestion, DivisionID) VALUES (@queQuestion, @DivisionID)");

        cmd.Parameters.Add("@queQuestion", MySqlDbType.VarChar, 250);
        cmd.Parameters["@queQuestion"].Value = qp.QueQuestion;

        cmd.Parameters.Add("@DivisionID", MySqlDbType.Int32);
        cmd.Parameters["@DivisionID"].Value = qp.DivisionId;

        conn.Open();

        result = cmd.ExecuteNonQuery();
        cmd.Dispose();
        conn.Close();
        return result;
    }

	public QuestionBLL()
	{

	}
}